Lex VOR Action Information Export

Introduction

An Export that consists of the information relative to the enquiries/vehicles that are off road.

Purpose

Lex have an export that provides information related to vehicles that are off road. This export needs to be generated by ViSN to ensure continuity in terms of process. This export will be a delta report consisting of detailed information relative to the action that is recorded against a job (enquiry) and enabling lex to monitor the progress of vehicles that are off the road."

File format

Comma Separated Values - *.csv

Terminology

• Action : The Follow up status recorded on VOR in ViSN is referred to as 'Action' by Lex
• Delta : Lex categorised this export as delta export as it is a detailed report
• Incident Type : The occurrence of an action against a enquiry is incident and the follow up status type on the action shall be treated as incident type. Further the incident type is recorded in exports with lex specific codes

Records

S.No	Column Name	Description	Type	length	Mandatory	Example
1	VOR_REFERENCE	Enquiry Number in ViSN	varchar	20	Y	50110
		Although this field is of type varchar, enquiry number of type integer is recorded If an enquiry is moved or reassigned to another supplier, same enquiry number shall be used for both of enquiries On a given day if multiple actions recorded against the enquiry the records shall be batched with the enquiry number
2	VEHICLE_REG	VRM	AS12DON	10
3	SITE_ID	Customer Supplier ID	int	20
4	Open_date	vehicle checked-In date and time	datetime		Y	17-01-2023 08:00
		date shall be formatted to dd-MM-yyyy hh:mm and will be in 24hr format
5	close_date	vor closure	datetime		N	17-01-2023 17:23
		Date and time when enquiry progressed to completed status or 'Vehicle Completed' is chosen as follow up status date shall be formatted to dd-MM-yyyy hh:mm and will be in 24hr format the field will be left blank for the enquiries that are not completed on the date of export
6	Driver_Name	driver full name recorded while creating an enquiry	varchar	110	N	George Leo
		The field will be left blank if no driver information is provided when creating an enquiry
7	INCIDENT_TYPE	incident type reference	int	5	Y	4018
		Will be one of the Incident Types Incident Types Incident Type Reference Incident Type (Follow Up Status) 4010 Automatic VOR 4011 Accident collision incident 4012 Investigation of problem 4013 Manufacturer investigation 4014 Manufacturer Recall 4015 Parts delay 4016 Parts of back order 4017 Problem cannot be identified 4018 Referred to manufacturer 4019 Vehicle being quality checked 4020 Vehicle being repaired 4021 Vehicle completed 4022 Vehicle has been recovered- investigation 4023 Warranty repair being identified 4024 Workshop cannot identify the problem 4025 Workshop capacity - full 4026 Workshop not capable of fixing the vehicle 4027 Moving the vehicle to another supplier Will be treated 4010 for actions without follow up status in them
8	Address	Lease company's Address	varchar	260	Y	Heathside Park, Heathside Park Rd, Stockport, Cheshire, SK30RB
		The lease company address will remain unchanged unless it's updated before an action is recorded.
9	MOBILE_PHONE_NO	driver's phone number	varchar	20	N	0127845899
		The field will be left blank if no driver information is provided when creating an enquiry
10	TELEPHONE_NO	customer's phone number	varchar	20	N	0127845899
		The phone number shall be picked from Customer contract → Customer Details section – Customer Number
11	EMAIL_ADDRESS	Customer's email address	varchar	250	N	test@digitalinnk.com
		The field will be left blank if no driver information is provided when creating an enquiry
12	BOOKING_ID	enquiryNumber	int		Y	5090
		If an enquiry is moved or reassigned to another supplier, same enquiry number shall be used for both of enquiries On a given day if multiple actions recorded against the enquiry the records shall be batched with the enquiry number
13	Comments	comments added when recording action	varchar	2000	N	need the vehicle before monday, 15th sep please
		Comments entered at time of enquiry creation or Notes added in VOR notes drawer If a user enters special characters such as commas(,), double quotes("), or single quotes(`) in a comment, the comment will be surrounded by quotation marks marking the start and end of the data in the field of csv
13	Comment_Entered_on	date when comments are recorded	datetime		N	17-01-2023 08:00
		If comments entered at time of enquiry creation or Notes added in VOR notes drawer, date shall be recorded date shall be formatted to dd-MM-yyyy hh:mm and will be in 24hr format
13	ESTIMATED_AVAILABLE_DATE	date when the action is expected to be accomplished and vehicle will be available	datetime		N	17-01-2023 08:00
		Will be the date that is chosen in the VOR Notes drawer Shall be left blank if no date is chosen Shall be left blank for auto VOR records as well

Data source

At the time of adding the vor note to the enquiry, a record consisting of export relative information is created at location

/leasecompany/[leaseId]/exports/vorActions/[year]/[month]/[date]/[enquiryId]

Setup Required

Database Configuration

This export is targeted for Lex lease companies alone, Hence storing these records for other companies is not required.

To prevent the records generated for non-Lex customers, A flag needs to be enabled in the lease company's export configuration to restrict the data export to Lex customers only

location: /leasecompany/[leaseId]/config/exportsConfig
attribute: vorActionExport
value: true

Generate Export

A Scheduler should be created cloud scheduler with the following parameters

• Name : Lex-GenerateDailyVorActionInformationExportCSV
• Description : Daily VOR Action Information Export Generation For Lex
• Region : europe-west2
• Frequency : 30 23 * * *
• TimeZone : Greenwhich Mean Time (GMT)
• Topic : projects/biddirect-2/topics/generateDailyVorActionInformationExport
• Message body : a JSON object including the following parameters
  • dateTimeStamp : in format yyyy-MMM-dd ex 2022-Sep-30 , when skipped it will default to current date
  • leaseId : id of the lease company that the export is desired to be generated for

Upload to SFTP

A Scheduler should be created cloud scheduler with the following parameters

• Name : Lex-UploadDailyVorActionInformationExportToSFTP
• Region : europe-west2
• Frequency : 45 23 * * *
• TimeZone : Greenwhich Mean Time (GMT)
• Topic : projects/biddirect-2/topics/uploadFileToSFTP
• Message body : a JSON object including the following parameters
  • sftpHostAddress : public hosting address of the sftp server
  • username : username for password authentication (ssh key not supported)
  • password : passkey for password authentication
  • bucketName : name of the firebase cloud storage
  • sourceFolderName : location in the firebase cloud storage to pick the file to copy to SFTP from
  • sourceFileName : regular expression that match to filename to pick file(s)
  • destinationFolderName : location in the sftp server
  • emailId : email id to receive the status in case of successful upload
  • errorEmailId : email id to receive the status in case of failed upload
  • emailSubject : generic subject that caters to both successful and failed uploads

Status: Accepted
Category: Protected
Authored By: Rama on 11 Sep, 2024

Revisions
11 Sep, 2024 - Create Documentation for vor action export, VN-14016